from flask import Flask,request
import pandas as pd
import json
import pymysql
import numpy as np
import threading
from db_connect import *

#################################################

tag = ['login_freq',
       'buy_mean',
       'login_night',
       'risk_index',
       'bank_trust_index',
       'rtqw_index',
       'payment_index',
       'social_act_index',
       'swjj_index'
       ]

ind = ['login_freq',
       'buy_mean',
       'login_night',
       'risk_index',
       'bank_trust_index',
       'rtqw_index',
       'payment_index',
       'social_act_index',
       'swjj_index',
       'open_count'
       ]

tag_out = ['risk_index',
           'bank_trust_index',
           'rtqw_index',
           'payment_index',
           'social_act_index',
           'swjj_index',
           'close_relation_index',
           'incentive_index'
           ]

fin = ['lsfx_index',
       'fund_index',
       'nhg_index',
       'swjj_index',
       'close_relation_index',
       'hvalue_customer_index',
       'bank_trust_index',
       'qbfe_index',
       'incentive_index',
       'rtqw_index',
       'social_act_index',
       'user_id',
       'gslc_index',
       'payment_index']

input_field = ['user_id',
               'gender',
               'age',
               'login_freq',
               'buy_mean',
               'open_count',
               'login_night',
               'risk_index',
               'bank_trust_index',
               'rtqw_index',
               'payment_index',
               'social_act_index',
               'swjj_index',
               'hvalue_customer_index',
               'gslc_index',
               'nhg_index',
               'fund_index',
               'lsfx_index',
               'qbfe_index']

line = '(' + ','.join(fin) + ')'

rule = pd.read_pickle('param.pkl')
rul = rule.loc[ind].values
rul_out = rule.loc[tag_out].values
##############################连接数据库####################################
db = MySQLBase()
cur = db.connect()

##################################################################
def get_data():
    '''取数据'''
    l = input_field
    li = ','.join(l)
    #conn = pymysql.connect(host=host1, user=user1, password=password1, db=db1, port=port1)
    #cursor = conn.cursor()

    command = "select " + li + " from " + table1
    # cursor.execute(command)
    # a = cursor.fetchall()
    a = db.query(command=command)
    z = pd.DataFrame(list(a), columns=l)
    return z

def establish_table():
    '''建表'''
    #conn = pymysql.connect(host=host1, user=user1, password=password1, db=db1, port=port1)
    #cursor = conn.cursor()
    command = "drop table if exists " + table2
    #cursor.execute(command)
    db.execute(command=command)
    command = "CREATE TABLE " + table2 + "(\
		user_id VARCHAR(256),\
		bank_trust_index FLOAT,\
		rtqw_index FLOAT,\
		payment_index FLOAT,\
		social_act_index FLOAT,\
		swjj_index FLOAT,\
          close_relation_index FLOAT,\
          incentive_index FLOAT,\
		hvalue_customer_index INT,\
           nhg_index INT,\
		gslc_index INT,\
          fund_index INT,\
		lsfx_index INT,\
		qbfe_index FLOAT\
	)"

    #cursor.execute(command)
    db.execute(command=command)

def score(x):
    '''转换成标准分'''
    y = 0.5 + 0.25 * (3 * x - x ** 3)
    return y

def guiyi(x):
    '''归一化'''
    t = x.dot(x.T)[0, 0]
    if t == 0:
        return x
    else:
        return x / np.sqrt(t)


def fenwei(ser, n=10):
    '''统计整个分位数'''
    v = list(ser.values)
    v.sort()
    N = len(v)
    l = []
    for i in range(1, n):
        l.append(v[int(i / n * N)])
    return l

def cal(x, dist):
    '''计算单个体分位数'''
    n = len(dist) + 1
    for i in range(n - 1):
        if x <= dist[i]:
            return (i + 1) * 1.0 / n
    return 1.0

def ser_to_ocean(ser):
    '''数据到OCEAN'''
    pool = {}
    for it in tag:
        w = cal(ser[it], dic[it])
        pool[it] = w
    it = 'open_count'
    w = ser[it]
    pool[it] = w
    out = pd.DataFrame([pool])[ind]
    v = out.values - 0.5
    p = guiyi(v.dot(rul))
    return p

def ser_complete(ser):
    p = ser_to_ocean(ser)
    sim = rul_out.dot(p.T)
    scor = score(sim) * 100
    se_new = {}
    for i in range(len(tag_out)):
        se_new[tag_out[i]] = scor[i, 0]

    ret = {}
    ret['user_id'] = ser['user_id']

    #####以下是行为型#####
    remain = ['hvalue_customer_index',
              'nhg_index',
              'gslc_index',
              'lsfx_index',
              'qbfe_index'
              ]
    for it in remain:
        ret[it] = ser[it]

    if ser['fund_index'] == 0:
        ret['fund_index'] = 0
    else:
        risk = ser['risk_index'] * 0.7 + se_new['risk_index'] * 0.3
        ret['fund_index'] = 5 - int(risk / 20)

    #####以下是特征型#####
    only = ['close_relation_index', 'incentive_index']
    for it in only:
        ret[it] = se_new[it]

    #####以下是混合型#####
    it = 'bank_trust_index'
    ret[it] = 0.5 * ser[it] + 0.5 * se_new[it]

    it = 'rtqw_index'
    ret[it] = 0.3 * ser[it] + 0.7 * se_new[it]

    it = 'payment_index'
    ret[it] = 0.5 * ser[it] + 0.5 * se_new[it]

    it = 'social_act_index'
    ret[it] = 0.3 * ser[it] + 0.7 * se_new[it]

    it = 'swjj_index'
    ret[it] = min(0.7 * ser[it] + 0.3 * se_new[it], 100)

    #####规整输出#####
    need_int = ['hvalue_customer_index',
                'gslc_index',
                'nhg_index',
                'fund_index',
                'lsfx_index'
                ]
    for k in ret.keys():
        if k == 'user_id':
            continue
        elif k in need_int:
            ret[k] = int(ret[k])
        else:
            ret[k] = round(ret[k], 2)

    return ret

def generate_tags():

    ############补标签并写库######################
    for i in z.index:
        ser = z.loc[i]
        ret = ser_complete(ser)
        print('数据生成 写入数据')
        st = '('
        for j in fin:
            a = ret[j]
            if type(a) == str:
                st = st + "'" + a + "'" + ','
            else:
                st = st + str(a) + ','
        st = st[:-1]
        st = st + ')'
        command = "insert into " + table2 + line + " values" + st
        print(command)
        db.insert(command=command)

        print('写入第 %d 条记录' % i)
        #cursor.execute(command)
        #conn.commit()


############################################################
app = Flask(__name__)   #创建1个Flask实例

@app.route('/generate')      #路由系统生成
def generate():
    print('loading')
    try:
        thd = threading.Thread(target=generate_tags)
        thd.start()
    except Exception as e:
        print(e)
    print('写入完成')
    dict={}
    dict['msg'] = 'ok'
    return json.dumps(dict)

@app.route('/uid_tag')
def uid_tag():
    uid = request.args.get("uid")  #从网址中获取参数uid
    print(uid)

    command = "select * from " + table2 + " where user_id= '" + uid + "'"
    # command = "select * from final_tag_4"
    # conn = pymysql.connect(host=host1, user=user1, password=password1, db=db1, port=port1)
    # cursor = conn.cursor()
    data = db.query(command=command)
    print('取数完成')

    dict = {}
    dict['user_id'] = data[0][0]
    dict['tags'] = data[0][1:]
    return json.dumps(uid)


if __name__ == '__main__':
    ################建表与取数##################
    print('建表中...')
    establish_table()
    print('建表完毕!')
    print('取数中...')
    z = get_data()
    print('取数完毕!')
    ############计算分位数##################
    print('分位数计算中...')
    dic = {}
    for it in tag:
        dic[it] = fenwei(z[it])
    print('分位数计算完毕!')

    app.run(host='0.0.0.0', port=5000) #启动socket